﻿#define  YzDB
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace hoemwork
{
    class DbHelper
    {
      
        private const string connectionString =
            @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=18bang;Integrated Security=True;";
        public IDbConnection Connection
        {
            get
            {
                return new SqlConnection(connectionString);
            }

        }
        public IDbConnection GetNewConnection()
        {
            return new SqlConnection(connectionString);
        }
        // 封装过后的ExecuteNonQuery()
        public int executeNonQuery(string cmdText, params IDataParameter[] parameters)
        {
            //声明connection对象赋值给GetNewConnection方法同时获取连接字符串连接数据库
            IDbCommand command = new SqlCommand();//声明命令的对象
            command.CommandText = cmdText;
            for (int i = 0; i < parameters.Length; i++)
            {
                command.Parameters.Add(parameters[i]);
            }
            return executeNonQuery(command);
            //command的属性赋值然后在执行方法
        }
        public int executeNonQuery(IDbCommand command)
        {
            if (command.Connection == null)
            {
                command.Connection = GetNewConnection();
            }//else use current connection
            try
            {
                if (command.Connection.State == ConnectionState.Closed)
                {
                    command.Connection.Open();
                }
                return command.ExecuteNonQuery();
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {

            }



            ////声明connection对象赋值给GetNewConnection方法同时获取连接字符串连接数据库
            //using (IDbConnection connection = GetNewConnection())
            //{
            //    try
            //    {
            //        connection.Open();
            //        return command.ExecuteNonQuery();
            //    }
            //    catch (Exception)
            //    {
            //        //专门针对数据库异常处理
            //        throw;
            //    }

            //}
        }
        //封装过后的ExecuteScalar()
        public object executeScalar(string cmdText, params IDataParameter[] parameters)
        {
            //声明connection对象赋值给GetNewConnection方法同时获取连接字符串连接数据库
            IDbCommand command = new SqlCommand();//声明命令的对象
            command.CommandText = cmdText;
            for (int i = 0; i < parameters.Length; i++)
            {
                command.Parameters.Add(parameters[i]);
            }
            return executeScalar(command);
        }
        public object executeScalar(IDbCommand command)
        {
            if (command.Connection == null)
            {
                command.Connection = GetNewConnection();
            }//else use current connection
            try
            {
                if (command.Connection.State == ConnectionState.Closed)
                {
                    command.Connection.Open();
                    object result = command.ExecuteScalar();
                    return result;
                }
                return null;
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {

            }
        }
        //ExecuteReader()方法
        public IDataReader excuteReader(string cmdText, params IDataParameter[] parameters)
        {
            //声明connection对象赋值给GetNewConnection方法同时获取连接字符串连接数据库
            IDbCommand command = new SqlCommand();//声明命令的对象
            command.CommandText = cmdText;
            for (int i = 0; i < parameters.Length; i++)
            {
                command.Parameters.Add(parameters[i]);
            }
            return excuteReader(command);


        }
        public IDataReader excuteReader(IDbCommand command)
        {
            if (command.Connection == null)
            {
                command.Connection = GetNewConnection();
            }//else use current connection
            try
            {
                if (command.Connection.State == ConnectionState.Closed)
                {
                    command.Connection.Open();
                    return command.ExecuteReader();
                    
                }
                return null;
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {

            }
        }


        public int Delete(string cmdText, params IDataParameter[] parameters)
        {
            return executeNonQuery(cmdText);
        }
        public void DeleteRange(IDbCommand[] commands)
        {
            using (IDbConnection connection = GetNewConnection())
            {
                for (int i = 0; i < commands.Length; i++)
                {
                    commands[i].Connection = connection;
                    commands[i].ExecuteNonQuery();

                }
            }
        }
        public void DeleteRange(string cmdText, params IDataParameter[] parameters)
        {
            IDbCommand[] commands = new SqlCommand[parameters.Length]; //声明commands数组
            for (int i = 0; i < parameters.Length; i++)
            {
                commands[i] = new SqlCommand                   //sql命令循环赋值给commands数组的值
                {
                    CommandText = cmdText                   //给commands属性commandtext赋值 
                };
                commands[i].Parameters.Add(parameters[i]); //依次为每个commands添加参数
            }
            DeleteRange(commands);
        }

        public IDataReader Get(string cmdText, IDbConnection connection)
        {

            return new SqlCommand().ExecuteReader();

        }
        private void executeNonQuery(IDbCommand dbCommand, IDbConnection connection)
        {
            using (connection)
            {
                dbCommand.ExecuteNonQuery();
            }
        }


    }
}
